using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Model;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraGrid.Views.Grid.ViewInfo;
using DevExpress.XtraGrid.Views.Base;
using FirebirdSql.Data.FirebirdClient;

namespace FirebirdTool
{
    public partial class FbTable : DevExpress.XtraEditors.XtraUserControl
    {
        public event FbEventHandler SavedEvent = null;
        private List<Column> lstColumn = null;
        private bool isNew = false;
        private string tableName = null;
        private string realTableName = null;
        private int count = 0;
        //-----------------------------------------------------------

        #region form
        public FbTable(string tableName, string realTableName, bool isNew)
        {
            InitializeComponent();

            this.tableName = tableName;
            this.realTableName = realTableName;

            this.txtTableName.Text = this.tableName;
            this.isNew = isNew;

            if (this.isNew)
                this.lstColumn = new List<Column>();
            else
                this.lstColumn = Util.GetListColumn(realTableName);

            this.bds.DataSource = this.lstColumn;

            this.btnPK.Visible = isNew;
            this.mnuPK.Visible = isNew;


            this.KeyDown += new KeyEventHandler(FbTable_KeyDown);
            this.gridView.FocusedRowChanged += new FocusedRowChangedEventHandler(gridView_FocusedRowChanged);

            this.btnSave.Click += new EventHandler(btnSave_Click);

            this.btnPK.Click += new EventHandler(btnPK_Click);
            this.mnuPK.Click += new EventHandler(btnPK_Click);

            this.mnuMoveUp.Click += new EventHandler(mnuMoveUp_Click);
            this.mnuMoveDown.Click += new EventHandler(mnuMoveDown_Click);

            this.btnInsert.Click += new EventHandler(btnInsert_Click);
            this.mnuInsert.Click += new EventHandler(btnInsert_Click);

            this.btnDelete.Click += new EventHandler(btnDelete_Click);
            this.mnuDelete.Click += new EventHandler(btnDelete_Click);

            this.repCmbDataType.Items.Add("bit");
            this.repCmbDataType.Items.Add("binary");
            this.repCmbDataType.Items.Add("datetime");
            this.repCmbDataType.Items.Add("float");
            this.repCmbDataType.Items.Add("int");
            this.repCmbDataType.Items.Add("image");
            this.repCmbDataType.Items.Add("long");
            this.repCmbDataType.Items.Add("nvarchar(50)");
            this.repCmbDataType.Items.Add("short");
            this.repCmbDataType.Items.Add("text");

            this.gridView.ActiveFilterString = "Deleted=0";
            this.gridView.ShowingEditor += new CancelEventHandler(gridView_ShowingEditor);
            this.gridView.RowCellStyle += new RowCellStyleEventHandler(gridView_RowCellStyle);

            this.gridControl.MouseDown += new MouseEventHandler(gridControl_MouseDown);
            this.gridView.KeyDown += new KeyEventHandler(gridView_KeyDown);

            this.timer1.Tick += new EventHandler(timer1_Tick);
        }

        private void FbTable_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.S && Control.ModifierKeys == Keys.Control)
                Save();
        }
        #endregion form
        //-----------------------------------------------------------

        #region control
        private void timer1_Tick(object sender, EventArgs e)
        {
            this.pnInfo.Visible = false;
            this.timer1.Enabled = false;
            this.timer1.Stop();
        }
        #endregion control

        #region button
        private void mnuMoveUp_Click(object sender, EventArgs e)
        {
            MoveRow(this.gridView.FocusedRowHandle, true);
        }

        private void mnuMoveDown_Click(object sender, EventArgs e)
        {
            MoveRow(this.gridView.FocusedRowHandle, false);
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            Save();
        }

        private void btnPK_Click(object sender, EventArgs e)
        {
            PK();
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            Insert();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            Delete();
        }
        #endregion button
        //-----------------------------------------------------------

        #region grid
        private void gridView_KeyDown(object sender, KeyEventArgs e)
        {
            string s = "";
            int i = 0;
            int index = 0;

            if (e.KeyCode == Keys.Delete && Control.ModifierKeys == Keys.Control)
            {
                Delete();
                e.Handled = true;
            }
            else
                if (e.KeyCode == Keys.S && Control.ModifierKeys == Keys.Control)
                {
                    Save();
                    e.Handled = true;
                }
                else
                    if (e.KeyCode == Keys.Up && Control.ModifierKeys == Keys.Control)
                    {
                        MoveRow(this.gridView.FocusedRowHandle, true);
                        e.Handled = true;
                    }
                    else
                        if (e.KeyCode == Keys.Down && Control.ModifierKeys == Keys.Control)
                        {
                            MoveRow(this.gridView.FocusedRowHandle, false);
                            e.Handled = true;
                        }
                        else
                            if (e.KeyCode == Keys.C && Control.ModifierKeys == Keys.Control)
                            {
                                int[] arrRowHandler = this.gridView.GetSelectedRows();
                                for (i = 0; i < arrRowHandler.Length; i++)
                                {
                                    index = arrRowHandler[i];
                                    if (index >= 0)
                                    {
                                        //Name|DataType|AllowNull|Description
                                        s += this.gridView.GetRowCellDisplayText(index, "Name") + "|";
                                        s += this.gridView.GetRowCellDisplayText(index, "DataType") + "|";
                                        s += this.gridView.GetRowCellDisplayText(index, "AllowNull") + "|";
                                        s += this.gridView.GetRowCellDisplayText(index, "Description") + "~";
                                    }
                                }

                                if (s != "")
                                    Clipboard.SetText("FB01.00:" + s);
                                e.Handled = true;
                            }
                            else
                                if (e.KeyCode == Keys.V && Control.ModifierKeys == Keys.Control)
                                {
                                    int rowHandler = this.gridView.FocusedRowHandle;
                                    s = Clipboard.GetText();

                                    if (string.IsNullOrEmpty(s))
                                        return;

                                    if (s.StartsWith("FB01.00:"))
                                    {
                                        s = s.Remove(0, "FB01.00:".Length);
                                        string[] arrS = s.Split('~');
                                        string[] arrS2 = null;
                                        for (i = 0; i < arrS.Length; i++)
                                        {
                                            arrS2 = arrS[i].Split('|');
                                            if (arrS2.Length == 4)
                                            {
                                                if (rowHandler < 0)
                                                {
                                                    Column newColumn = new Column();
                                                    newColumn.Name = arrS2[0];
                                                    newColumn.DataType = arrS2[1];
                                                    newColumn.AllowNull = (arrS2[2] != "Unchecked");
                                                    newColumn.Description = arrS2[3];
                                                    this.lstColumn.Add(newColumn);
                                                }
                                                else
                                                {
                                                    this.gridView.SetRowCellValue(rowHandler, "Name", arrS2[0]);
                                                    this.gridView.SetRowCellValue(rowHandler, "DataType", arrS2[1]);
                                                    this.gridView.SetRowCellValue(rowHandler, "AllowNull", (arrS2[2] != "Unchecked"));
                                                    this.gridView.SetRowCellValue(rowHandler, "Description", arrS2[3]);

                                                    if (rowHandler == this.gridView.RowCount - 1)
                                                        rowHandler = -1;
                                                    else
                                                        rowHandler = this.gridView.GetVisibleRowHandle(rowHandler + 1);
                                                }
                                            }
                                        }

                                        this.gridView.RefreshData();
                                        e.Handled = true;
                                    }
                                }
        }

        private void gridControl_MouseDown(object sender, MouseEventArgs e)
        {
            Point hitPoint = this.gridControl.PointToClient(Control.MousePosition);
            GridHitInfo hitInfo = this.gridView.CalcHitInfo(hitPoint);

            if (e.Button == MouseButtons.Right)
                if (hitInfo.InRow)
                {
                    bool PK = Util.GetBool(this.gridView.GetRowCellValue(hitInfo.RowHandle, "PK"));
                    if (PK == false)
                        this.mnuPK.Text = "Set Primary Key";
                    else
                        this.mnuPK.Text = "Remove Primary Key";

                    this.menu.Show(this.gridControl, new Point(e.X, e.Y));
                }
        }

        private void gridView_RowCellStyle(object sender, RowCellStyleEventArgs e)
        {
            if (this.isNew == false)
            {
                int ID = Util.GetInt(this.gridView.GetRowCellValue(e.RowHandle, "ID"), -1);
                if (ID < 0)
                    e.Appearance.ForeColor = Color.Green;
            }
        }

        private void gridView_ShowingEditor(object sender, CancelEventArgs e)
        {
            //int ID = Util.GetInt(this.gridView.GetFocusedRowCellValue("ID"), -1);
            //if (ID >= 0 && this.gridView.FocusedColumn.FieldName != "Description")
            //    e.Cancel = true;
        }

        private void gridView_FocusedRowChanged(object sender, FocusedRowChangedEventArgs e)
        {
            bool PK = Util.GetBool(this.gridView.GetFocusedRowCellValue("PK"));
            if (PK == false)
                this.btnPK.Text = "Set Primary Key";
            else
                this.btnPK.Text = "Remove Primary Key";
        }
        #endregion grid
        //-----------------------------------------------------------

        #region function
        private void Save()
        {
            int i = 0;
            int length = 0;
            string fullDesc = "";
            this.tableName = this.txtTableName.Text;

            if (this.isNew)
                this.realTableName = this.txtTableName.Text.ToUpper();

            string PKColumn = null;
            Column column = null;
            Column column2 = null;
            string scmd = "";
            string scmdPK = "";
            string serror = "";

            #region validate
            if (tableName == "")
            {
                MessageBox.Show("Enter Table name", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.txtTableName.Focus();
                return;
            }

            if (tableName.Length > 30)
            {
                MessageBox.Show("Table name must not longer than 30 character", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.txtTableName.Focus();
                return;
            }

            if (this.gridView.FocusedColumn.FieldName == "ColumnName")
                this.gridView.FocusedColumn = this.colDataType;
            else
                this.gridView.FocusedColumn = this.colColumnName;

            this.bds.EndEdit();
            this.lstColumn = (List<Column>)this.bds.DataSource;

            for (i = 0; i < this.lstColumn.Count; i++)
            {
                if (this.lstColumn[i].ID < 0)
                    this.lstColumn[i].RealName = this.lstColumn[i].Name.ToUpper();

                column = this.lstColumn[i];

                if (column.PK)
                    PKColumn = column.RealName;
            }

            if (PKColumn == null)
            {
                MessageBox.Show("Set Primary Key", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.gridView.Focus();
                return;
            }
            #endregion validate
            //=============================================

            #region rebuilt position
            UpdatePosition();

            List<Column> tmpLstColumn = new List<Column>();

            bool copy = false;
            length = this.lstColumn.Count;
            for (i = 0; i < length; i++)
            {
                copy = false;
                column = this.lstColumn[i];

                if (column.ID < 0 && column.Deleted == false)
                    copy = true;
                else
                    if (column.ID >= 0)
                        copy = true;

                if (copy)
                {
                    Column copyColumn = column.Clone();
                    tmpLstColumn.Add(copyColumn);
                }
            }

            column = new Column();
            column2 = new Column();
            length = tmpLstColumn.Count;
            for (i = 0; i < length - 1; i++)
            {
                column = tmpLstColumn[i].Clone();

                for (int j = i + 1; j < length; j++)
                {
                    column2 = tmpLstColumn[j].Clone();

                    if (column.Position > column2.Position)
                    {
                        tmpLstColumn[i] = column2.Clone();
                        tmpLstColumn[j] = column.Clone();
                        column = tmpLstColumn[i].Clone();
                    }
                }
            }

            this.lstColumn = tmpLstColumn;
            #endregion rebuilt position
            //=============================================

            length = this.lstColumn.Count;
            if (this.isNew)
            {
                #region create table
                scmd = "CREATE TABLE " + this.realTableName;
                scmd += "(";
                for (i = 0; i < length; i++)
                {
                    column = this.lstColumn[i];
                    scmd += column.GetColumnSQL() + ",\r\n";

                    if (column.PK)
                        scmdPK = "PRIMARY KEY (" + column.RealName + ")";
                }
                scmd += scmdPK;
                scmd += ");";

                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create Table", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                //create table's description
                scmd = "UPDATE RDB$RELATIONS SET RDB$DESCRIPTION = '" + this.tableName + "' WHERE RDB$RELATION_NAME = '" + this.realTableName + "';";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create Table Description", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                #endregion create table
                //--------------------------------------

                #region create description
                for (i = 0; i < length; i++)
                {
                    column = this.lstColumn[i];
                    fullDesc = "{" + column.Name + "}";
                    if (string.IsNullOrEmpty(column.Description) == false)
                        fullDesc += column.Description;

                    scmd = "UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = '" + fullDesc + "' WHERE RDB$FIELD_NAME = '" + column.RealName + "' AND RDB$RELATION_NAME = '" + this.realTableName + "';";
                    serror = Util.ExecNonQuery(scmd);
                    if (serror != null)
                    {
                        MessageBox.Show(serror, "Create Description " + column.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }
                #endregion create description
                //--------------------------------------

                #region create autoinc
                scmd = "CREATE GENERATOR GEN_" + this.realTableName + "_ID;";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create Generator", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                scmd = "SET GENERATOR GEN_" + this.realTableName + "_ID TO 0;";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Set Generator", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                scmd = "CREATE TRIGGER " + this.realTableName + "_BI FOR " + this.realTableName;
                scmd += "\r\nACTIVE BEFORE INSERT POSITION 0";
                scmd += "\r\nAS";
                scmd += "\r\nBEGIN";
                scmd += "\r\nif (NEW." + PKColumn + " is NULL) then NEW." + PKColumn + " = GEN_ID(GEN_" + this.realTableName + "_ID, 1);";
                scmd += "\r\nEND";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Create Triggrer", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                #endregion create autoinc
                //--------------------------------------

                #region set grant
                scmd = "GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON " + this.realTableName + " TO SYSDBA WITH GRANT OPTION;";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Set Grant", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                #endregion set grant
            }
            else
            {
                #region update table
                scmd = "COMMENT ON TABLE " + this.realTableName + " IS '" + this.tableName + "';";
                serror = Util.ExecNonQuery(scmd);
                if (serror != null)
                {
                    MessageBox.Show(serror, "Update Table Description", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                #endregion update table
                //--------------------------------------

                #region delete column
                for (i = 0; i < length; i++)
                {
                    column = this.lstColumn[i];
                    if (column.Deleted)
                    {
                        scmd = "ALTER TABLE " + this.realTableName + " DROP " + column.RealName + ";";

                        serror = Util.ExecNonQuery(scmd);
                        if (serror != null)
                        {
                            MessageBox.Show(serror, "Drop column " + column.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                    }
                }
                #endregion delete column
                //--------------------------------------

                #region add column
                for (i = 0; i < length; i++)
                {
                    column = this.lstColumn[i];

                    if (column.ID == -1)
                    {
                        scmd = "ALTER TABLE " + this.realTableName + " ADD " + column.GetColumnSQL() + ";";
                        serror = Util.ExecNonQuery(scmd);
                        if (serror != null)
                        {
                            MessageBox.Show(serror, "Add column " + column.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                    }
                }
                #endregion add column
                //--------------------------------------

                #region update description
                for (i = 0; i < length; i++)
                {
                    column = this.lstColumn[i];
                    if (column.Deleted == false)
                    {
                        fullDesc = "{" + column.Name + "}";
                        if (string.IsNullOrEmpty(column.Description) == false)
                            fullDesc += column.Description;

                        scmd = "COMMENT ON COLUMN " + this.realTableName + "." + column.RealName + " is '" + fullDesc + "';";
                        serror = Util.ExecNonQuery(scmd);
                        if (serror != null)
                        {
                            MessageBox.Show(serror, "Update description " + column.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                    }
                }
                #endregion change description
                //--------------------------------------

                #region reorder
                for (i = 0; i < length; i++)
                {
                    column = this.lstColumn[i];
                    if (column.Deleted == false)
                    {
                        scmd = "ALTER TABLE " + this.realTableName + " ALTER " + column.RealName + " POSITION " + column.Position + ";";
                        serror = Util.ExecNonQuery(scmd);
                        if (serror != null)
                        {
                            MessageBox.Show(serror, "Reorder", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                    }
                }
                #endregion reorder
                //--------------------------------------
            }

            this.isNew = false;
            this.lstColumn = Util.GetListColumn(this.realTableName);

            this.btnPK.Visible = false;
            this.mnuPK.Visible = false;

            this.bds.DataSource = this.lstColumn;
            this.gridView.RefreshData();

            this.count++;
            this.lblInfo.Text = "Saved " + this.count;
            this.pnInfo.Visible = true;
            this.Update();

            this.timer1.Enabled = true;
            this.timer1.Start();

            if (this.SavedEvent != null)
            {
                FbEventArgs evt = new FbEventArgs(this.lstColumn, this.tableName);
                this.SavedEvent(this, evt);
            }
        }

        private void PK()
        {
            if (this.btnPK.Visible)
            {
                bool PK = Util.GetBool(this.gridView.GetFocusedRowCellValue("PK"));
                if (PK)
                    this.gridView.SetFocusedRowCellValue("PK", false);
                else
                {
                    for (int i = 0; i < this.gridView.RowCount; i++)
                    {
                        int index = this.gridView.GetVisibleRowHandle(i);
                        this.gridView.SetRowCellValue(index, "PK", false);
                    }

                    this.gridView.SetFocusedRowCellValue("PK", true);
                    this.gridView.SetFocusedRowCellValue("AllowNull", false);
                }
            }
        }

        private void Insert()
        {
            Column column = new Column();

            int rowhandle = this.gridView.FocusedRowHandle;
            if (rowhandle == int.MinValue + 1)
                rowhandle = this.lstColumn.Count;
            else
                column.Position = Util.GetInt(this.gridView.GetRowCellValue(rowhandle, "Position")) - 1;

            this.lstColumn.Add(column);

            this.gridView.RefreshData();
            this.gridView.FocusedRowHandle = rowhandle;
        }

        private void Delete()
        {
            int rowhandle = this.gridView.FocusedRowHandle;
            if (rowhandle >= 0)
            {
                bool PK = Util.GetBool(this.gridView.GetFocusedRowCellValue("PK"));
                int ID = Util.GetInt(this.gridView.GetFocusedRowCellValue("ID"));
                if (PK && ID >= 0)
                {
                    XtraMessageBox.Show("Cannot delete Primary Key", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                if (string.IsNullOrEmpty(this.gridView.GetFocusedRowCellDisplayText("Name")))
                {
                    this.gridView.SetFocusedRowCellValue("Deleted", true);
                    this.gridView.RefreshData();
                }
                else
                    if (XtraMessageBox.Show("Delete column " + this.gridView.GetFocusedRowCellDisplayText("Name"), "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        this.gridView.SetFocusedRowCellValue("Deleted", true);
                        this.gridView.RefreshData();
                    }
            }
        }

        private void UpdatePosition()
        {
            this.gridView.CheckLoaded();
            int length = this.gridView.RowCount;
            for (int i = 0; i < length; i++)
                this.gridView.SetRowCellValue(i, "Position", i + 1);
        }

        private void MoveRow(int rowHandler, bool moveUp)
        {
            if (rowHandler < 0)
                return;

            if (rowHandler == 0 && moveUp)
                return;

            if (moveUp == false)
            {
                if (rowHandler == this.gridView.RowCount)
                    return;

                if (this.gridView.GetVisibleRowHandle(rowHandler + 1) < 0)
                    return;
            }

            UpdatePosition();

            int newRowHandler = rowHandler + (moveUp ? -1 : 1);

            int oldPos = Util.GetInt(this.gridView.GetRowCellValue(rowHandler, "Position"));
            int newPos = Util.GetInt(this.gridView.GetRowCellValue(newRowHandler, "Position"));

            this.gridView.SetRowCellValue(rowHandler, "Position", newPos);
            this.gridView.SetRowCellValue(newRowHandler, "Position", oldPos);

            this.gridView.RefreshData();
            this.gridView.FocusedRowHandle = newRowHandler;
        }
        #endregion function
    }
}
